package it.unical.mat.aec.persistence;

import it.unical.mat.aec.model.Domanda;
import it.unical.mat.aec.model.Traccia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.HashMap;


import com.mysql.jdbc.Statement;

public class GestoreDb {
	
	private ConnectionDB connection;
	
	private final String SELECT_RISPOSTA="SELECT Test FROM answer WHERE idAnswer=?;";
	private final String SELECT_RISPOSTA_CORRETTA="SELECT idAnswer FROM associationQA WHERE idQuestion=?;";
	private final String SELECT_DOMANDA="SELECT Text , PointIfCorrect FROM question WHERE idQuestion=?;  ";
	private final String SELECT_TRACCIA="SELECT IdQuestion FROM test WHERE idTest=?;  ";
	private final String SELECT_TRACCE="SELECT DISTINCT idTest FROM test;";
	
	
	public GestoreDb() {
		connection=new ConnectionDB();
	}
	
	public HashMap<Integer, Traccia> getTracce() throws PersistanceException{
		Connection con=null;
		Statement statement=null;
		HashMap<Integer, Traccia> tracce=new HashMap<Integer, Traccia>();
		try{
			con=connection.getConnection();
			statement=(Statement) con.createStatement();
			ResultSet result=statement.executeQuery(SELECT_TRACCE);
			while(result.next()){
				int id=result.getInt("idTest");
				Traccia traccia=getTraccia(id);
				tracce.put(id, traccia);
			}
		}catch (SQLException e) {
			throw new PersistanceException("Error db:"+e.getLocalizedMessage());
		}
		return tracce;
	}
	
	public Traccia getTraccia(int idTraccia) throws PersistanceException{
		Connection con=null;
		PreparedStatement statement=null;
		Traccia traccia=null;
		try {
			con=connection.getConnection();
			statement=con.prepareStatement(SELECT_TRACCIA);
			statement.setInt(1, idTraccia);
			ResultSet result=statement.executeQuery();
			while(result.next()){
				if(traccia==null)
					traccia=new Traccia(idTraccia);
				int idDomanda=result.getInt("IdQuestion");
				traccia.aggiungiDomanda(getDomanda(idDomanda));
			}
					
		} catch (SQLException e) {
			throw new PersistanceException("Error db :"+e.getLocalizedMessage());
		}
		return traccia;
	}
	

	
	
	public Domanda getDomanda(int idDomanda) throws PersistanceException{
		Connection con=null;
		PreparedStatement statement=null;
		Domanda domanda=null;
		try {
			con=connection.getConnection();
			statement=con.prepareStatement(SELECT_DOMANDA);
			statement.setInt(1, idDomanda);
			ResultSet result=statement.executeQuery();
			while(result.next()){
				String testoDomanda=result.getString("Text");
				Double punteggio=result.getDouble("PointIfCorrect");
				String testoRisposta=getRisposta(idDomanda);
				domanda=new Domanda(idDomanda, testoDomanda, testoRisposta, punteggio);
			}
					
		} catch (SQLException e) {
			throw new PersistanceException("Error db :"+e.getLocalizedMessage());
		}
		return domanda;

	}
	
	public String getRisposta(int idDomanda) throws PersistanceException{
		Connection con=null;
		PreparedStatement statement=null;
		String risposta=null;
		try {
			int id=0;
			con=connection.getConnection();
			statement=con.prepareStatement(SELECT_RISPOSTA_CORRETTA);
			statement.setInt(1, idDomanda);
			ResultSet result=statement.executeQuery();
			while(result.next())
				id=result.getInt("idAnswer");
			if(id!=0){
			statement=con.prepareStatement(SELECT_RISPOSTA);
			statement.setInt(1, id);
			result=statement.executeQuery();
			while(result.next())
				risposta=result.getString("Test");
			}
			} catch (SQLException e) {
				throw new PersistanceException("Error db :"+e.getLocalizedMessage());
			}
					return risposta;
	}
	
	
}
